Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Microsoft.Practices.EnterpriseLibrary.Data.Sql

Imports System.Data.Common

Namespace Seuss.EnterpriseLibrary.DataAccess

    Public Class DAL_Academico

#Region "Atributos"
        Dim Db As Database
#End Region

#Region "Constructores"
        Public Sub New()
            Db = DatabaseFactory.CreateDatabase
        End Sub

        Public Sub New(ByVal NewDb As Database)
            If Db Is Nothing Then
                Db = DatabaseFactory.CreateDatabase
            Else
                Db = NewDb
            End If
        End Sub
#End Region

#Region "Propiedades"

        ReadOnly Property Database() As Database
            Get
                Return Db
            End Get
        End Property

#End Region

#Region "Metodos: Acceso a Datos"

#Region "Otros..."

        Public Function Get_Academico_DocRegistroNotas_By_Seccion(ByVal nCurGruCodigo As Long) As DbCommand
            Try

                Dim strSql As String
                strSql = "SELECT DocRegNota.cDocCodigo, DocRegNota.nSecCodigo, DocRegNota.cDocRegFormula, DocRegNota.bFavorece, " & _
                                "Documentos.dDocFecha , Documentos.cDocObserv " & _
                         "FROM DocRegNota INNER JOIN Documentos ON DocRegNota.cDocCodigo = Documentos.cDocCodigo " & _
                         "WHERE nCurGruCodigo = " & nCurGruCodigo

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(strSql)
                Return Cmd

            Catch ex As Exception
                Throw ex
            End Try
        End Function

        Public Function Get_Academico_Seccion_By_Asignatura_Perido(ByVal nCurCodigo As Long, _
                                                                   ByVal nCicCodigo As Long, _
                                                                   ByVal nMenCodigo As Long, _
                                                                   ByVal nAsiCodigo As Long, _
                                                                   ByVal nPrdCodigo As Long) As DbCommand
            Try

                Dim strSql As String
                strSql = "SELECT CurGrupo.nCurGruCodigo, Seccion.cConDescripcion, CurGrupo.nCurCodigo, CurGrupo.nCicCodigo, CurGrupo.nMenCodigo, " & _
                                "CurGrupo.nAsiCodigo, CurGrupo.nPrdCodigo, Grupos.nGruCodigo, Grupos.cGruNombre, Persona.cPerCodigo, " & _
                                "Persona.cPerApellido + ', ' + Persona.cPerNombre as Nombre " & _
                         "FROM Constante Seccion INNER JOIN CurGrupo INNER JOIN Grupos ON CurGrupo.nCurGruCodigo = Grupos.nCurGruCodigo ON " & _
                              "Seccion.nConValor = CurGrupo.nSecCodigo AND Seccion.nConCodigo = " & Constante.Seccion & " LEFT OUTER JOIN " & _
                              "CurDetPersonas INNER JOIN Persona ON CurDetPersonas.cPerCodigo = Persona.cPerCodigo ON Grupos.nGruCodigo = CurDetPersonas.nGruCodigo " & _
                         "WHERE (Grupos.nGruEstado = 1) AND (CurGrupo.nCurCodigo = " & nCurCodigo & ") AND (CurGrupo.nCicCodigo = " & nCicCodigo & _
                               ") AND (CurGrupo.nMenCodigo = " & nMenCodigo & ") AND (CurGrupo.nAsiCodigo = " & nAsiCodigo & _
                               ") AND (CurGrupo.nPrdCodigo = " & nPrdCodigo & ") " & _
                         "ORDER BY Seccion.cConDescripcion, Grupos.cGruNombre, Persona.cPerApellido + ', ' + Persona.cPerNombre "

                Dim Cmd As DbCommand = Db.GetSqlStringCommand(strSql)
                Return Cmd

            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Function Get_Academico_Unidades_By_Registro(ByVal cDocCodigo As Long) As DbCommand
            Try
                Dim StrSql As String = "" & _
                    "SELECT RegSubPeriodo.nSubPrdCodigo, " & _
                           "Constante.cConDescripcion " & _
                    "FROM RegSubPeriodo INNER JOIN " & _
                         "Constante ON " & _
                            "RegSubPeriodo.nSubPrdCodigo = Constante.nConValor AND " & _
                            "Constante.nConCodigo = " & Constante.SubPeriodo & " " & _
                    "WHERE RegSubPeriodo.cDocCodigo = @cDocCodigo " & _
                    "ORDER BY RegSubPeriodo.nSubPrdCodigo"
                Dim Cmd As DbCommand = Db.GetSqlStringCommand(StrSql)
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function
#End Region

#Region "Evaluaciones"


        Public Function Get_Evaluacion_TipoNota_By_Registro_And_SubPeriodo(ByVal cDocCodigo As String, _
                                                                           ByVal nSubPrdCodigo As Long) As DbCommand
            Try
                Dim Cmd As DbCommand = Db.GetStoredProcCommand("sp_Evaluacion_TipoNota_Get_By_Registro_SubPeriodo")
                Db.AddInParameter(Cmd, "cDocCodigo", System.Data.DbType.String, cDocCodigo)
                Db.AddInParameter(Cmd, "nSubPrdCodigo", System.Data.DbType.Int32, nSubPrdCodigo)
                Return Cmd
            Catch ex As DbException
                Throw ex
            End Try
        End Function

#End Region

#End Region

    End Class

End Namespace
